package com.simple.accounting.db;

import android.accounts.Account;
import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.simple.accounting.utils.FloatUtils;

import java.util.ArrayList;
import java.util.List;

/*
 * 管理数据库的类
 * 主要对于账单表当中的内容进行增删改查
 * */
public class DBManager {

    private static SQLiteDatabase db;

    /* 初始化数据库对象*/
    public static void initDB(Context context) {
        /*帮助类对象*/
        DBOpenHelper helper = new DBOpenHelper(context);
        /*获取数据库对象*/
        db = helper.getWritableDatabase();
    }


    /**
     * 读取数据库当中的数据，写入内存集合里
     *
     * @param kind 表示收入或者支出
     * @return
     */
    public static List<TypeBean> getTypeList(int kind) {
        List<TypeBean> list = new ArrayList<>();
        //读取type_tb表当中的数据
        String sql = "select * from type_tb where kind = " + kind;
        @SuppressLint("Recycle")
        Cursor cursor = db.rawQuery(sql, null);
        //循环读取游标内容，存储到对象当中
        while (cursor.moveToNext()) {
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int imageId = cursor.getInt(cursor.getColumnIndex("imageId"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind1 = cursor.getInt(cursor.getColumnIndex("kind"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            TypeBean typeBean = new TypeBean(id, typename, imageId, sImageId, kind1);
            list.add(typeBean);
        }
        return list;
    }


    /**
     * 向账单表中插入一条数据
     *
     * @param bean 账单实体类
     */
    public static void insertItemToAccountTb(AccountBean bean) {
        ContentValues values = new ContentValues();
        values.put("typename", bean.getTypename());
        values.put("sImageId", bean.getsImageId());
        values.put("remarks", bean.getRemarks());
        values.put("money", bean.getMoney());
        values.put("time", bean.getTime());
        values.put("year", bean.getYear());
        values.put("month", bean.getMonth());
        values.put("day", bean.getDay());
        values.put("kind", bean.getKind());
        db.insert("account_tb", null, values);
    }


    /**
     * 更新账单表中的数据
     *
     * @param bean 账单实体类
     */
    public static void updateItemToAccountTb(AccountBean bean) {
        ContentValues values = new ContentValues();
        values.put("typename", bean.getTypename());
        values.put("sImageId", bean.getsImageId());
        values.put("remarks", bean.getRemarks());
        values.put("money", bean.getMoney());
        values.put("time", bean.getTime());
        values.put("year", bean.getYear());
        values.put("month", bean.getMonth());
        values.put("day", bean.getDay());
        values.put("kind", bean.getKind());
        db.update("account_tb", values, "id = " + bean.id, null);
    }


    /**
     * 获取所有的账单记录
     *
     * @return 账单实体类集合
     */
    public static List<AccountBean> getAllAccountListFromAccountTb() {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account_tb";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String remarks = cursor.getString(cursor.getColumnIndex("remarks"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, remarks, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        cursor.close();

        return list;
    }


    /**
     * 获取记账表当中某一天的所有支出或者收入情况
     *
     * @param year  年
     * @param month 月
     * @param day   日
     * @return 账单实体类集合
     */
    public static List<AccountBean> getAccountListOneDayFromAccountTb(int year, int month, int day) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account_tb where year=? and month=? and day=? order by id desc";
        @SuppressLint("Recycle")
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + ""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String remarks = cursor.getString(cursor.getColumnIndex("remarks"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, remarks, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }


    /**
     * 获取某年某月的所有收入支出情况
     *
     * @param year  年
     * @param month 月
     * @return 账单实体类集合
     */
    public static List<AccountBean> getAccountListOneMonthFromAccountTb(int year, int month) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account_tb where year=? and month=? order by id desc";
        /*使用Cursor遍历数据库中的每一行数据*/
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + ""});
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String remarks = cursor.getString(cursor.getColumnIndex("remarks"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, remarks, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }


    /**
     * 获取某一天的收入/支出的总金额
     *
     * @param year  年
     * @param month 月
     * @param day   日
     * @param kind  类型
     * @return 总金额
     */
    public static float getSumMoneyOneDay(int year, int month, int day, int kind) {
        float total = 0.0f;
        String sql = "select sum(money) from account_tb where year=? and month=? and day=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "", kind + ""});
        // 遍历
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }


    /**
     * 获取某一月的收入/支出的总金额
     *
     * @param year  年
     * @param month 月
     * @param kind  支出==0    收入===1
     * @return 总金额
     */
    public static float getSumMoneyOneMonth(int year, int month, int kind) {
        float total = 0.0f;
        String sql = "select sum(money) from account_tb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /**
     * 统计某月份支出或者收入笔数 收入(1) 支出(0)
     */
    public static int getCountItemOneMonth(int year, int month, int kind) {
        int total = 0;
        String sql = "select count(money) from account_tb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            int count = cursor.getInt(cursor.getColumnIndex("count(money)"));
            total = count;
        }
        return total;
    }

    /**
     * 获取某一年的支出或者收入的总金额   收入(1) 支出(0)
     */
    public static float getSumMoneyOneYear(int year, int kind) {
        float total = 0.0f;
        String sql = "select sum(money) from account_tb where year=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", kind + ""});
        // 遍历
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }


    /**
     * 根据id删除记录
     *
     * @param id 账单id
     * @return
     */
    public static int deleteItemFromAccountTbById(int id) {
        int i = db.delete("account_tb", "id=?", new String[]{id + ""});
        return i;
    }

    /**
     * 根据备注搜索收入或者支出的情况列表
     */
    public static List<AccountBean> getAccountListByRemarkFromAccountTb(String remarks) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account_tb where remarks like '%" + remarks + "%'";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String remark = cursor.getString(cursor.getColumnIndex("remarks"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, remark, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }

    /**
     * 根据类型搜索收入或者支出的情况列表
     */
    public static List<AccountBean> getAccountListByTypeFromAccountTb(String typename1) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account_tb where typename = ?";
        Cursor cursor = db.rawQuery(sql, new String[]{typename1});
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String remarks = cursor.getString(cursor.getColumnIndex("remarks"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, remarks, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }

    /**
     * 根据类型搜索收入或者支出的情况列表
     */
    public static List<AccountBean> getAccountListByTimeFromAccountTb(String time1) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account_tb where time like '%" + time1 + "%'";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String remarks = cursor.getString(cursor.getColumnIndex("remarks"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, remarks, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }


    /**
     * 根据花费搜索收入或者支出的情况列表
     */
    public static List<AccountBean> getAccountListByMoneyFromAccountTb(float money1) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account_tb where money = " + money1;
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String remarks = cursor.getString(cursor.getColumnIndex("remarks"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, remarks, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }

    /**
     * 查询记账的表当中有几个年份信息
     */
    public static List<Integer> getYearListFromAccountTb() {
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(year) from account_tb order by year asc";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            list.add(year);
        }
        return list;
    }

    /*
     * 删除account_tb表格当中的所有数据
     * */
    public static void deleteAllAccount() {
        String sql = "delete from account_tb";
        db.execSQL(sql);
    }

    /**
     * 查询指定年份和月份的收入或者支出每一种类型的总钱数
     */
    public static List<ChartItemBean> getChartListFromAccountTb(int year, int month, int kind) {
        List<ChartItemBean> list = new ArrayList<>();
        float sumMoneyOneMonth = getSumMoneyOneMonth(year, month, kind);  //求出支出或者收入总钱数
        String sql = "select typename,sImageId,sum(money)as total from account_tb where year=? and month=? and kind=? group by typename " +
                "order by total desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        while (cursor.moveToNext()) {
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            float total = cursor.getFloat(cursor.getColumnIndex("total"));
            //计算所占百分比  total /sumMonth
            float ratio = FloatUtils.div(total, sumMoneyOneMonth);
            ChartItemBean bean = new ChartItemBean(sImageId, typename, ratio, total);
            list.add(bean);
        }
        return list;
    }

    /**
     * 获取这个月当中某一天收入支出最大的金额，金额是多少
     */
    public static float getMaxMoneyOneDayInMonth(int year, int month, int kind) {
        String sql = "select sum(money) from account_tb where year=? and month=? and kind=? group by day order by sum(money) desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            return money;
        }
        return 0;
    }

    /**
     * 根据指定月份每一日收入或者支出的总钱数的集合
     */
    public static List<BarChartItemBean> getSumMoneyOneDayInMonth(int year, int month, int kind) {
        String sql = "select day,sum(money) from account_tb where year=? and month=? and kind=? group by day";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        List<BarChartItemBean> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            float smoney = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            BarChartItemBean itemBean = new BarChartItemBean(year, month, day, smoney);
            list.add(itemBean);
        }
        return list;
    }

}
